﻿# -*- coding: utf-8 -*-
"""
Created on Sun Sep  4 10:11:22 2022

@author: huangyue
"""

import pymssql
# import argparse

# import sys
import datetime
import pandas as pd
# import numpy as np
import matplotlib.pyplot as plt


plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 连接聚源的参数
server_jydb = "10.10.0.102"
user_jydb = "jydb"
password_jydb = "jydb"



begdate = '2010-01-01'
enddate = datetime.datetime.now().strftime('%Y-%m-%d')

d1 = datetime.timedelta(days=1)


# %% 聚源相关数据提取
def get_PublicOfferingBond(cursor):
    # 公募债代码
    str_sql = '''
       	select distinct InnerCode from Bond_ConBDIssue
       	where IssueMethod not like '%%私募%%'
       	and IssueMethod not like '%%法人定向配售%%'
        '''
    # 数据提取
    cursor.execute(str_sql)
    df = pd.DataFrame(cursor.fetchall(),columns=['InnerCode'])        
    return df

def get_IssueSize(cursor):
    # 转债发行规模
    str_sql = '''
        SELECT 
        Bond_ConBDBasicInfo.InnerCode,
        --Bond_Code.SecuCode as CBCode,
        --Bond_Code.SecuAbbr as CBAbbr,
        Bond_Code.ListedDate,
        Bond_Code.DelistDate,
        ActualIssueSize
        FROM Bond_Code,Bond_ConBDBasicInfo
        where 
        Bond_Code.InnerCode=Bond_ConBDBasicInfo.InnerCode
        and  Bond_Code.SecuMarket in (90,83) --仅保留上交所深交所
        and BondForm in (1,4)  --同时保留可转债、可交债
        order by InnerCode'''
    # 数据提取
    cursor.execute(str_sql)
    basicinfo = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','ListedDate','DelistDate','IssueSize'])
    return basicinfo

def get_amountdata(cursor):
    # 余额数据    
    str_amt='''
    SELECT InnerCode,EndDate,EventType,IssueAmount,RemainingAmount
    FROM Bond_ConBDConvertInfo'''
    
    # 数据提取
    cursor.execute(str_amt)
    amtdata = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','EndDay','EvenType','IssueAmt','RemainAmt'])
    return amtdata

def get_ratingdata(cursor, InnerCodes):
    '''
    编号与评级的对应关系
    '''
    CRCode2CRDesc = {1000: 'AAA+',
                    999: 'AAA',
                    990: 'AAA-',
                    980: 'AA+',
                    970: 'AA',
                    960: 'AA-',
                    950: 'A+',
                    940: 'A',
                    930: 'A-',
                    899: 'BBB+',
                    895: 'BBB',
                    891: 'BBB-',
                    880: 'BB+',
                    870: 'BB',
                    860: 'BB-',
                    850: 'B+',
                    840: 'B',
                    830: 'B-',
                    799: 'CCC',
                    770: 'CC',
                    760: 'C',
                    740: 'D'}

    # (1)转债评级：以上市日为起点，提取初始评级信息    
    str_rate='''
        SELECT InnerCode,CreditRating,CreditRatingCode, ListedDate
        FROM Bond_ConBDBasicInfo'''
    
    # 数据提取
    cursor.execute(str_rate)
    ratingData_initial = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','CreditRating','CreditRatingCode','ListedDate'])
    
    str_InnerCodes = str(InnerCodes)[1:-1]
    
    # (2)转债评级：不断更新的评级公告
    str_rate='''
        select MainCode,InfoPublDate,CRCode,CRDesc 
        from Bond_BDCreditGrading
        where MainCode in (%s)
        '''%(str_InnerCodes)
    
    # 数据提取
    cursor.execute(str_rate)
    ratingData = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','date','CRCode','CreditRating'])    
    
    # (3)数据合并
    ratingData = pd.concat([ratingData, ratingData_initial.rename(columns={'ListedDate':'date','CreditRatingCode':'CRCode'})], axis = 0)
    ratingData = ratingData.sort_values(by = ['InnerCode','date']).reset_index(drop=True)    
    
    # 存在一些AA+u、AAk、A+k的情况，将其按照CRCode修改成标准评级语言
    ratingData['CreditRating'] = ratingData['CRCode'].replace(CRCode2CRDesc)

    return ratingData

def get_Priceenddata(cursor):
    # 转债赎回价格、补偿利率
    str_rate='''
        SELECT InnerCode,CallPriceEnd,CompRate
        FROM Bond_ConBDBasicInfo'''
    
    # 数据提取
    cursor.execute(str_rate)
    Priceenddata = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','CallPriceEnd','CompRate'])
    return Priceenddata

def get_enddatedata(cursor):
    # 上市时间与到期时间
    str_enddate='''
        SELECT InnerCode,EndDate,ListedDate
        FROM Bond_ConBDBasicInfo'''
    
    # 数据提取
    cursor.execute(str_enddate)
    amtdata = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','EndDate','ListedDate'])
    return amtdata





# 转债代码与简称
def get_bondname(cursor):    
    str_sql = '''
        SELECT 
        Bond_ConBDBasicInfo.InnerCode,
        Bond_Code.SecuCode as CBCode,
        Bond_Code.SecuAbbr as CBAbbr,
        BondForm,
		Bond_Code.SecuMarket
        FROM Bond_Code,Bond_ConBDBasicInfo
        where 
        Bond_Code.InnerCode=Bond_ConBDBasicInfo.InnerCode
        and  Bond_Code.SecuMarket in (90,83) --仅保留深交所90，上交所83
        and BondForm in (1,4)  --同时保留可转债、可交债
        order by InnerCode
        '''
    # 数据提取    
    cursor.execute(str_sql)    
    df = pd.DataFrame(cursor.fetchall(),columns=['InnerCode','BondCode','BondAbbr','BondForm','SecuMarket'])       
    return df

# 转债行情
def get_CB_quote(cursor,begdate=None,enddate=None):
    str_columns = 'InnerCode,TradingDay,ClosePrice,NewConvetPrice,StockPrice'.replace(' ','')  # 删除所有空格
    list_columns = str_columns.split(',')    
    
    if begdate is None:
        begdate = '2017-01-01'
    
    if not isinstance(begdate,str):
        begdate = begdate.strftime('%Y-%m-%d')
        
    if enddate is None:
        str_sql = '''
        select %s
        from Bond_ConBDExchangeQuote
        where TradingDay>='%s'
            '''% (str_columns,begdate)
    else:
        if not isinstance(enddate,str):
            enddate = enddate.strftime('%Y-%m-%d')
        str_sql = '''
        select %s
        from Bond_ConBDExchangeQuote
        where TradingDay between '%s' and '%s'
            '''% (str_columns,begdate,enddate)

    cursor.execute(str_sql)
    tmp_table = pd.DataFrame(cursor.fetchall(),columns=list_columns)\
                  .rename(columns={'TradingDay':'date','ClosePrice':'CBprice'})\
                  .astype({'NewConvetPrice':float,'CBprice':float,'StockPrice':float})
    return tmp_table  

# 提取行业信息
def get_IndustryInfo(cursor):
    # 主板
    str_sql1 = '''
            select a.CompanyCode,AShareAbbr,AStockCode,InfoPublDate,FirstIndustryName,ThirdIndustryName from
            (select  InfoPublDate, CompanyCode, FirstIndustryName ,ThirdIndustryName
            from LC_ExgIndustry
            where Standard = 37) as a
            inner join
            (select CompanyCode,AShareAbbr,AStockCode from LC_StockArchives) as b
            on a.CompanyCode = b.CompanyCode
            '''
    # 科创板
    str_sql2 = '''
            select a.CompanyCode,AShareAbbr,AStockCode,InfoPublDate,FirstIndustryName,ThirdIndustryName from
            (select  InfoPublDate, CompanyCode, FirstIndustryName ,ThirdIndustryName
            from LC_STIBExgIndustry
            where Standard = 37) as a
            inner join
            (select CompanyCode,AShareAbbr,AStockCode from LC_StockArchives) as b
            on a.CompanyCode = b.CompanyCode
            '''
    # 数据提取    
    cursor.execute(str_sql1)    
    df1 = pd.DataFrame(cursor.fetchall(),
                      columns=['CompanyCode','AShareAbbr','AStockCode','InfoPublDate','FirstIndustryName','ThirdIndustryName'])
    cursor.execute(str_sql2)    
    df2 = pd.DataFrame(cursor.fetchall(),
                      columns=['CompanyCode','AShareAbbr','AStockCode','InfoPublDate','FirstIndustryName','ThirdIndustryName'])   
    df = pd.concat([df1,df2])    
    return df

# 转债正股名称代码
def get_CB_Stock(cursor):
    str_sql = '''
            select InnerCode,SecuCode,SecuAbbr,StockInnerCode,CompanyCode from Bond_ConBDBasicInfo
            '''
    # 数据提取    
    cursor.execute(str_sql)    
    df = pd.DataFrame(cursor.fetchall(),
                      columns=['InnerCode','SecuCode','SecuAbbr','StockInnerCode','CompanyCode'])\
        .dropna().astype({'StockInnerCode':int,'InnerCode':int,'CompanyCode':int})   
    # CompanyCode用于对其行业信息
    return df


# 债券余额+公募债
def cal_bond_amt(issuesize,amt2,CBcode_POB):
    # (1)处理债券发行情况：保留公募债
    issuesize = issuesize[issuesize['InnerCode'].isin(list(CBcode_POB.values.T[0]))]
    # (2)处理债券余额信息
    issuesize['IssueAmt_Beg'] = issuesize['IssueSize']*1000000  # 上市时的余额
    issuesize['IssueAmt_End'] = 0   # 退市时的余额
    amt1 = pd.concat([issuesize[['InnerCode','ListedDate','IssueAmt_Beg']]\
            .rename(columns={'ListedDate':'EndDay','IssueAmt_Beg':'IssueAmt'}),
            issuesize[['InnerCode','DelistDate','IssueAmt_End']]\
            .rename(columns={'DelistDate':'EndDay','IssueAmt_End':'IssueAmt'})])\
            .dropna()   # 主要删除上市日期尚未公布，或者退市日期尚未公布的观测
    # amt1 = amt1[amt1['EndDay']<=tradingdate]   # 删除到期日比较晚的数据
    amt1['EvenType'] = 0
    amt1['RemainAmt'] = amt1['IssueAmt']
    amt = pd.concat([amt2,amt1]).sort_values(by=['InnerCode','EndDay'])
        # .groupby('InnerCode').last()
    # amt = amt[(amt['RemainAmt']>0) &(amt['EndDay']>'2010-01-01')]   
    CBcode_amt = amt.reset_index(drop=True)
    return CBcode_amt  


'''提取中证转债指数'''
def get_ZZZZ_data(cursor,begdate,enddate=None):
    if enddate is None:
        str_sql = '''
            select TradingDay,ClosePrice from QT_IndexQuote
            where InnerCode = 19297
            and TradingDay > '%s'
            ORDER BY TradingDay
                '''% (begdate)
    else:
        str_sql = '''
            select TradingDay,ClosePrice from QT_IndexQuote
            where InnerCode = 19297
            and TradingDay between '%s' and '%s'
            ORDER BY TradingDay
                '''% (begdate,enddate)

    cursor.execute(str_sql)
    tmp_table = pd.DataFrame(cursor.fetchall(), columns=['date','close']).astype({'close':float})          

    return tmp_table 

# 赎回信息
def get_callinfo(cursor):
    str_columns = 'InnerCode,CallStartDate,\
        CallEndDate,CallLevel,CallConditionDay,CallReachDay'.replace(' ','')  # 删除所有空格
    list_columns = str_columns.split(',')    
    str_sql = '''
		select distinct %s
		from Bond_ConBDCallInfo
        '''%(str_columns)
    # 数据提取    
    cursor.execute(str_sql)    
    df = pd.DataFrame(cursor.fetchall(),columns=list_columns)\
        .dropna()\
        .astype({'CallLevel':float,'CallConditionDay':int,'CallReachDay':int})
    return df

# 纯债收益率
def get_CB_YTM(cursor, begdate = '2016-01-01', enddate = '2023-12-31'):
    str_columns = 'TradingDay,InnerCode,YTM_CL'.replace(' ','')  # 删除所有空格
    list_columns = str_columns.split(',')  

    str_sql = '''
        select  %s 
        from Bond_CBDerivNew
        WHERE TradingDay between '%s' and '%s'
        ORDER BY TradingDay    
            '''%(str_columns, begdate, enddate)
    # 数据提取    
    cursor.execute(str_sql)    
    df = pd.DataFrame(cursor.fetchall(),
                      columns = list_columns)       
    return df

# 转债正股收益率
def get_CB_Stock_ret(cursor, stockInnerCode = [6,28],
                     begdate = '2016-01-01', enddate = '2023-12-31'):
    
    str_columns = 'InnerCode, TradingDay, ChangePCT'.replace(' ','')  # 删除所有空格
    list_columns = str_columns.split(',')  
    
    stockInnerCode_str = str(stockInnerCode)[1:-1]
    
    str_sql = '''
            select %s
            from QT_Performance
            where InnerCode in (%s)
            and TradingDay between '%s' and '%s'
            
            UNION
            
            select %s
            from LC_STIBPerformanceData
            where InnerCode in (%s)
            and TradingDay between '%s' and '%s'            
            
            '''%(str_columns, stockInnerCode_str, begdate, enddate,
                 str_columns, stockInnerCode_str, begdate, enddate)
    # 数据提取    
    cursor.execute(str_sql)    
    df = pd.DataFrame(cursor.fetchall(),columns = list_columns)       
    return df




# %% 数据提取

'''

conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='cp936')
cursor_jydb = conn_jydb.cursor()

# 转债名称、类别
allBondName = get_bondname(cursor_jydb)  

# 转债行情
CBquote = get_CB_quote(cursor_jydb,begdate = begdate,enddate = enddate) 

# 行业信息
IndustryInfo = get_IndustryInfo(cursor_jydb) 

# 转债余额调整
amt_change = get_amountdata(cursor_jydb)  

# 中证转债指数   
zzzz_data = get_ZZZZ_data(cursor_jydb,begdate = begdate)

# 转债正股
CB_stock = get_CB_Stock(cursor_jydb)

# 转债评级
ratingData = get_ratingdata(cursor_jydb)

# 转债到期日
enddateData = get_enddatedata(cursor_jydb)

# 到期赎回价+补偿利率
PriceendData = get_Priceenddata(cursor_jydb)

# 赎回条款
allClauseCall = get_callinfo(cursor_jydb)   

# 纯债收益率
CB_YTM = get_CB_YTM(cursor_jydb, begdate = begdate, enddate = enddate)
CB_YTM = CB_YTM.drop_duplicates(subset=['TradingDay','InnerCode'])

# 转债正股收益率
stockInnerCode = CB_stock['StockInnerCode'].dropna().astype(int)
stockInnerCode = list(set(stockInnerCode))

CB_stock_ret = get_CB_Stock_ret(cursor_jydb, stockInnerCode = stockInnerCode, begdate = begdate, enddate = enddate)

# 相应信息对应到转债代码innercode
innerCodeReplace = CB_stock[['StockInnerCode','InnerCode']].set_index('StockInnerCode').to_dict()
CB_stock_ret = CB_stock_ret.replace(innerCodeReplace)


# 关闭服务器
cursor_jydb.close()
conn_jydb.close()


'''



# (2) 如果sql语句里面有汉字，需要换编码
'''
conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='utf8')
cursor_jydb = conn_jydb.cursor()
# 公开发行的转债
CBcode_POB = get_PublicOfferingBond(cursor_jydb)     
# 转债发行规模
issuesize = get_IssueSize(cursor_jydb)       
# 关闭服务器    
cursor_jydb.close()
conn_jydb.close()
'''

# %% 数据整理
'''(1)转债截面信息：传出CBinfo'''

def get_CBinfo(cursor_jydb):
    # conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='cp936')
    # cursor_jydb = conn_jydb.cursor()
    
    # 转债名称、类别
    allBondName = get_bondname(cursor_jydb)  
    
    # 转债正股
    CB_stock = get_CB_Stock(cursor_jydb)
    
    # cursor_jydb.close()
    # conn_jydb.close()
    
    CBinfo = pd.merge(allBondName, CB_stock,on='InnerCode')
    
    # wind代码
    CBinfo['BondCode_wind'] = CBinfo['BondCode'] + CBinfo['SecuMarket'].replace({90:'.SZ',83:'.SH'})
    return CBinfo


'''(2)转债面板数据：传出anadata'''

def get_anadata(CBinfo, begdate = None,enddate = None):
    
    '''
    'date','BondCode','InnerCode',
    价格：'CBPrice','StockPrice','ConvetPrice','RemainScale','ParPrice','PremiumRate','ret'
    行业

    '''
    

    '''数据准备'''
    conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='cp936')
    cursor1 = conn_jydb.cursor()   
    # 行业信息：因为行业有可能会变，所以是面板数据
    IndustryInfo = get_IndustryInfo(cursor1) 
    # 转债行情
    CBquote = get_CB_quote(cursor1,begdate = begdate - d1*20,enddate = enddate)   # 多提取一个交易日的数据，以便计算收益率
    # 转债余额调整
    amt_change = get_amountdata(cursor1)      
    # 关闭服务器    
    cursor1.close()
    conn_jydb.close()   
    
    conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='utf8')
    cursor2 = conn_jydb.cursor() 
    # 公开发行的转债
    CBcode_POB = get_PublicOfferingBond(cursor2)  
    # 转债发行规模
    issuesize = get_IssueSize(cursor2)       
    # 关闭服务器    
    cursor2.close()
    conn_jydb.close()    
    
    
    '''数据处理'''
    # 转债余额
    CB_amt_all = cal_bond_amt(issuesize,amt_change,CBcode_POB)    
    
    anadata = pd.merge_asof(CBquote.sort_values(by='date'),
                            CB_amt_all[['InnerCode','EndDay','RemainAmt']].sort_values(by='EndDay'),
                            left_on='date',right_on='EndDay',by = 'InnerCode')\
        .sort_values(by=['InnerCode','date'])\
            .dropna().drop(columns='EndDay').reset_index(drop=True)
    
    # 转债平价
    anadata['ParPrice'] = anadata['StockPrice']/anadata['NewConvetPrice']*100
    # 转股溢价率
    anadata['PremiumRate'] = anadata['CBprice'] / anadata['ParPrice']-1
    # 加上转债代码以及简称
    anadata = anadata.merge(CBinfo[CBinfo['BondForm']==1][['InnerCode','BondCode_wind','StockInnerCode','CompanyCode']],on='InnerCode')
    # 行业信息
    anadata = pd.merge_asof(anadata.sort_values(by='date').astype({'StockInnerCode':'int32','CompanyCode':'int32',}),
                          IndustryInfo.rename(columns={'AStockCode':'SecuCode'}).sort_values(by='InfoPublDate'),
                          left_on='date',right_on='InfoPublDate',by='CompanyCode')
    
    # 表整理
    anadata = anadata.rename(columns={'RemainAmt':'RemainScale','NewConvetPrice':'ConvetPrice','CBprice':'CBPrice'})\
            .drop(columns=['CompanyCode','StockInnerCode','AShareAbbr','InfoPublDate','SecuCode','BondCode_wind'])\
            .astype({'RemainScale':float})
    
    # 按照余额筛选
    # anadata = anadata[anadata['RemainScale']>=350000000]
    
    # 计算收益率
    anadata = anadata.sort_values(by=['date','InnerCode']).set_index(['date','InnerCode'])
    anadata['ret'] = anadata['CBPrice'].groupby('InnerCode').pct_change().fillna(0)
    
    anadata= anadata.reset_index()     
    
    
    # 由于行情数据多提取了20个日历日，所以在此处删除
    anadata = anadata[anadata['date']>=begdate]
           
    return anadata




